package com.moorror.codegenvel.db.sqlite.dao;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.intellij.openapi.application.ApplicationManager;
import com.moorror.codegenvel.common.notification.CodeGenByVelocityNotifier;
import com.moorror.codegenvel.db.sqlite.DatabaseBasicService;
import com.moorror.codegenvel.db.sqlite.entity.AbEntity;
import com.moorror.codegenvel.utils.ListConvertUtils;
import com.moorror.codegenvel.utils.MMapUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.function.BiConsumer;
import java.util.function.BiFunction;
import java.util.function.Function;
import java.util.function.Supplier;

/**
 * 封装了针对于数据的通用操作
 * DAO: database access object: 数据库访问对象
 * @param <T>
 */
@SuppressWarnings("unchecked")
public abstract class BaseDAO<T> implements CommonDao<T>{
    private Class<T> clazz;
    protected QueryRunner queryRunner = new QueryRunner();


    public BaseDAO(){
        Class clazzTemp = this.getClass();//获取子类的类型
        //获取父类的类型
        //getGenericSuperclass() 代表获取父类的类型
        //parameterizedType 代表的是带泛型的类型
        ParameterizedType parameterizedType= (ParameterizedType) clazzTemp.getGenericSuperclass();
        //获取泛型类型的数组
        Type[] types = parameterizedType.getActualTypeArguments();
        this.clazz= (Class<T>) types[0];
    }

    //除开增删改查. 还需要sql语句查询比如总共有多少个用户之类的通用方法
    public <E> E getValue(Connection conn,String sql ,Object...args){
        E result = null;
        ScalarHandler<E> handler = new ScalarHandler<E>();
        try {
            result = queryRunner.query(conn, sql, handler,args);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }



    //查询多个对象--ver3.0
    public List<T> queryList(Connection conn, String sql, Object... objs) {
        List<T> list = null;
        //开启驼峰映射 因为数据库是 create_time .而java 类是createTime
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        BeanListHandler<T> handler = new BeanListHandler<T>(clazz,processor);
        try {
            list = queryRunner.query(conn, sql, handler, objs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //List<Object>每个列数据类型不同
    public List<String> queryTitleList(Connection conn, String sql, Object... objs){
        List<String> list = null;
        ColumnListHandler<String> handler =new ColumnListHandler<String>("title");
        try {
            list = queryRunner.query(conn, sql, handler,objs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //List<Object>每个列数据类型不同
    public List<String> queryImageRecords(Connection conn, String sql, Object... objs){
        List<String> list = null;
        ColumnListHandler<String> handler =new ColumnListHandler<String>("image_records");
        try {
            list = queryRunner.query(conn, sql, handler,objs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    //查询单个对象--ver3.0
    public T getBean(Connection conn, String sql, Object... objs) {
        T t = null;
        try {
            //开启驼峰映射
            BeanProcessor bean = new GenerousBeanProcessor();
            RowProcessor processor = new BasicRowProcessor(bean);
            t = queryRunner.query(conn,sql,new BeanHandler<T>(clazz,processor),objs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return t;
    }

    //增删改 通用--ver3.0
    public Integer update(Connection conn, String sql, Object... args) {
        int count = 0;
        try {
            count = queryRunner.update(conn, sql, args);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Throwable th){
            th.printStackTrace();
        }
        return count;
    }

    //修改多个对象的id;
    public void updateBatch(Connection conn, String sql, Object[][] args){
        try {
            queryRunner.batch(conn, sql, args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    public void insertArr(Connection conn, T[] ary) {
        insert(conn, ary);
    }

    /**
     *
     * @param conn
     * @param ts
     * @return
     */
    public boolean saveObj(Connection conn, List<T> ts) {
        try {
            List<T> notExist = Lists.newArrayList();
            for (T t : ts) {
                T tq = findByUnique(conn, t);
                if (null == tq) {
                    notExist.add(t);
                } else {
                    updateByUnique(conn, t);
                }
            }
            if (CollectionUtils.isEmpty(notExist)) {
                return true;
            }
            insertArr(conn, ListConvertUtils.toArray(notExist));
            return true;
        } catch (Exception ex) {
            CodeGenByVelocityNotifier.notifyException(ex);
            return false;
        }
    }

    /**
     *
     * @param ts
     * @return
     */
    public boolean saveObj(List<T> ts) {
        DatabaseBasicService databaseBasicService = ApplicationManager.getApplication().getService(DatabaseBasicService.class);
        Connection conn = null;
        try {
            conn = databaseBasicService.getSource().getConnection();
            return saveObj(conn, ts);
        } catch (Exception ex) {
            CodeGenByVelocityNotifier.notifyException(ex);
            return false;
        } finally {
            databaseBasicService.closeResource(conn, null, null);
        }
    }


    public static <T, R> R connWrap(Function<Connection, R> fun) {
        DatabaseBasicService databaseBasicService = ApplicationManager.getApplication().getService(DatabaseBasicService.class);
        Connection conn = null;
        try {
            conn = databaseBasicService.getSource().getConnection();
            return fun.apply(conn);
        } catch (Exception ex) {
            ex.printStackTrace();
            CodeGenByVelocityNotifier.notifyException(ex);
            return null;
        } finally {
            databaseBasicService.closeResource(conn, null, null);
        }
    }

    /**
     *
     * @param d impl
     * @param u 需要更新的对象，尽量按有没有值来更新
     * @param <U>
     * @param <D>
     */
    public static <U extends AbEntity, D extends CommonDao> void update(D d, U u) {
        Boolean re = BaseDAO.connWrap((conn) -> {
            return d.updateByUnique(conn, u);
        });
        if (null != re && re) {
            CodeGenByVelocityNotifier.notify("save " + d.tableName() + " success");
        } else {
            CodeGenByVelocityNotifier.notify("save " + d.tableName() + " fail");
        }
    }

    /**
     * 删除的对象对
     *
     * @param dus impl 及 需要删除的对象
     * @param <U>
     * @param <D>
     */
    public static <U extends AbEntity, D extends CommonDao> void delete(D d, U u) {
        delete(Lists.newArrayList(Pair.of(d, u)));
    }

    /**
     * 删除的对象对
     * @param dus impl 及 需要删除的对象
     * @param <U>
     * @param <D>
     */
    public static <U extends AbEntity, D extends CommonDao> void delete(List<Pair<D, U>> dus) {
        boolean[] last = {true};
        Boolean re = BaseDAO.connWrap((conn) -> {
            for (Pair<D, U> p : dus) {
                boolean check = p.getLeft().deleteByUnique(conn, p.getRight());
                if (!check) {
                    last[0] = check;
                }
            }
            return last[0];
        });
        if (last[0]) {
            CodeGenByVelocityNotifier.notify(" delete  success");
        } else {
            CodeGenByVelocityNotifier.notify(" delete fail");
        }
    }

    /**
     * 判断是否存在并添加
     * @param u 需要保存的对象
     * @param d impl
     * @param checkField 校验哪个属性
     * @param supplier form 表单，OK时回调
     * @return
     * @param <U>
     * @param <D>
     */
    public static <U extends AbEntity, D extends CommonDao> Pair<Boolean, String> checkExistAdd(U u, D d, Function<U, String> checkField, Supplier<Pair> supplier) {
        Boolean exist = BaseDAO.connWrap((conn) -> {
            return d.checkExist(conn, u);
        });
        if (null != exist && exist) {
            return Pair.of(false, checkField.apply(u) + " has exist ");
        }
        U ua = BaseDAO.connWrap((conn) -> {
            return (U) d.insert(conn, u);
        });
        if (null == ua) {
            return Pair.of(false, " add sqlite fail");
        }
        if (null != supplier) {
            return supplier.get();
        }
        return Pair.of(true, null);
    }

    /**
     * 多对多关系，按 like 查询
     * @param searchValue 查询的key
     * @param md impl
     * @param mKeyFun 关联的关键key
     * @param mnd impl
     * @param mnNKeyFun 关联对象关联的key
     * @param mnMKeyFun 关联对象关联的key
     * @param nd impl
     * @param nNKeyFun 另一个对象key
     * @param consumer 传入 MN 和 N, 拼出完整的 N, 为什么这么设计，减少对象，直接将 MN 转到 N 当中
     * @return
     * @param <M>
     * @param <MD>
     * @param <N>
     * @param <ND>
     * @param <MN>
     * @param <MND>
     */
    public static <M extends AbEntity, MD extends CommonDao, N extends AbEntity, ND extends CommonDao, MN extends AbEntity, MND extends CommonDao> List<M> manyToMany(String searchValue, MD md, Function<M, String> mKeyFun, MND mnd, Function<MN, String> mnNKeyFun, Function<MN, String> mnMKeyFun, ND nd, Function<N, String> nNKeyFun, BiConsumer<MN, N> consumer) {
        DatabaseBasicService databaseBasicService = ApplicationManager.getApplication().getService(DatabaseBasicService.class);
        Connection conn = null;
        List<M> ms = Lists.newArrayList();
        try {
            conn = databaseBasicService.getSource().getConnection();
            ms = md.searchLike(conn, searchValue);
            if (CollectionUtils.isEmpty(ms)) {
                return ms;
            }
            Set<String> keys = Sets.newHashSet();
            for (M m : ms) {
                String key = mKeyFun.apply(m);
                if (StringUtils.isNotEmpty(key)) {
                    keys.add(key);
                }
            }
            if (keys.size() <= 0) {
                return ms;
            }
            List<MN> mns = mnd.queryRelate(conn, Lists.newArrayList(keys));
            Set<String> keysN = Sets.newHashSet();
            if (CollectionUtils.isEmpty(mns)) {
                return ms;
            }
            Map<String, List<MN>> mMNMap = Maps.newHashMap();
            Set<String> nkeys = Sets.newHashSet();
            for (MN mn : mns) {
                String key = mnNKeyFun.apply(mn);
                if (StringUtils.isNotEmpty(key)) {
                    nkeys.add(key);
                }
                MMapUtils.fillMapList(mMNMap, mnMKeyFun.apply(mn), mn);
            }
            List<N> ns = nd.queryIn(conn, Lists.newArrayList(nkeys));
            Map<String, N> nMap = MMapUtils.fillMap(nNKeyFun, ns);

            for (M m : ms) {
                String key = mKeyFun.apply(m);
                List<MN> mnsT = mMNMap.get(key);
                if (CollectionUtils.isEmpty(mnsT)) {
                    continue;
                }
                for (MN mn : mnsT) {
                    String mnNKey = mnNKeyFun.apply(mn);
                    N n = nMap.get(mnNKey);
                    if (null != n) {
                        consumer.accept(mn, n);
                        m.addChildren(n);
                    }
                }
            }
            return ms;
        } catch (Exception ex) {
            CodeGenByVelocityNotifier.notifyException(ex);
            return ms;
        } finally {
            databaseBasicService.closeResource(conn, null, null);
        }
    }

    /**
     * 多对多删除
     * @param md
     * @param mnd
     * @param nd
     * @param m
     * @param convert
     * @return
     * @param <M>
     * @param <MD>
     * @param <N>
     * @param <ND>
     * @param <MN>
     * @param <MND>
     */
    public static <M extends AbEntity, MD extends CommonDao, N extends AbEntity, ND extends CommonDao, MN extends AbEntity, MND extends CommonDao> boolean manyToManyDelete(MD md, MND mnd, ND nd, M m, BiFunction<M, N, MN> convert) {
        DatabaseBasicService databaseBasicService = ApplicationManager.getApplication().getService(DatabaseBasicService.class);
        Connection conn = null;
        try {
            conn = databaseBasicService.getSource().getConnection();
            if (CollectionUtils.isNotEmpty(m.getChildren())) {
                for (AbEntity abEntity : m.getChildren()) {
                    N n = (N) abEntity;
                    MN mn = convert.apply(m, n);
                    if (null == mn) {
                        continue;
                    }
                    nd.deleteByUnique(conn, n);
                    mnd.deleteByUnique(conn, mn);
                }
            }
            md.deleteByUnique(conn, m);
        } catch (Exception ex) {
            CodeGenByVelocityNotifier.notifyException(ex);
            return false;
        } finally {
            databaseBasicService.closeResource(conn, null, null);
        }
        return true;
    }

    /**
     *
     * @param searchValue 查询关键字
     * @param md 主表DAO
     * @param mKeyFun 主key 如id或唯一key
     * @param nd 关联表 DAO
     * @param nMKeyFun N 当中获取到关联的key 即 mKey
     * @return
     * @param <M>
     * @param <MD>
     * @param <N>
     * @param <ND>
     */
    public static <M extends AbEntity, MD extends CommonDao, N extends AbEntity, ND extends CommonDao> List<M> oneToMany(String searchValue, MD md, Function<M, String> mKeyFun, ND nd, Function<N, String> nMKeyFun) {
        DatabaseBasicService databaseBasicService = ApplicationManager.getApplication().getService(DatabaseBasicService.class);
        Connection conn = null;
        List<M> ms = Lists.newArrayList();
        try {
            conn = databaseBasicService.getSource().getConnection();
            ms = md.searchLike(conn, searchValue);
            if (CollectionUtils.isEmpty(ms)) {
                return ms;
            }
            Set<String> keys = Sets.newHashSet();
            for (M m : ms) {
                String key = mKeyFun.apply(m);
                if (StringUtils.isNotEmpty(key)) {
                    keys.add(key);
                }
            }
            if (keys.size() <= 0) {
                return ms;
            }

            List<N> ns = nd.queryRelate(conn, Lists.newArrayList(keys));

            for (M m : ms) {
                String key = mKeyFun.apply(m);
                for(N n : ns){
                    String nmKey = nMKeyFun.apply(n);
                    if(nmKey.equals(key)){
                        m.addChildren(n);
                    }
                }
            }
            return ms;
        } catch (Exception ex) {
            CodeGenByVelocityNotifier.notifyException(ex);
            return ms;
        } finally {
            databaseBasicService.closeResource(conn, null, null);
        }
    }

    public static <M extends AbEntity, MD extends CommonDao, N extends AbEntity, ND extends CommonDao, P extends AbEntity, PD extends CommonDao> List<M> oneToManyToMany(String searchValue, MD md, Function<M, String> mKeyFun, ND nd, Function<N, String> nMKeyFun, Function<N, String> nKeyFun, PD pd, Function<P, String> pNKeyFun) {
        DatabaseBasicService databaseBasicService = ApplicationManager.getApplication().getService(DatabaseBasicService.class);
        Connection conn = null;
        List<M> ms = Lists.newArrayList();
        try {
            conn = databaseBasicService.getSource().getConnection();
            ms = md.searchLike(conn, searchValue);
            if (CollectionUtils.isEmpty(ms)) {
                return ms;
            }
            Set<String> keys = Sets.newHashSet();
            for (M m : ms) {
                String key = mKeyFun.apply(m);
                if (StringUtils.isNotEmpty(key)) {
                    keys.add(key);
                }
            }
            if (keys.size() <= 0) {
                return ms;
            }

            List<N> ns = nd.queryRelate(conn, Lists.newArrayList(keys));
            Set<String> nKeys = Sets.newHashSet();
            for (M m : ms) {
                String key = mKeyFun.apply(m);
                for(N n : ns){
                    String nKey = nKeyFun.apply(n);
                    nKeys.add(nKey);
                    String nmKey = nMKeyFun.apply(n);
                    if(nmKey.equals(key)){
                        m.addChildren(n);
                    }
                }
            }
            if(nKeys.isEmpty()){
                return ms;
            }
            List<P> ps = pd.queryRelate(conn, Lists.newArrayList(nKeys));
            for(N n : ns){
                String nKey = nKeyFun.apply(n);
                for(P p : ps){
                    String pnKey = pNKeyFun.apply(p);
                    if(pnKey.equals(nKey)){
                        n.addChildren(p);
                    }
                }
            }
            return ms;
        } catch (Exception ex) {
            CodeGenByVelocityNotifier.notifyException(ex);
            return ms;
        } finally {
            databaseBasicService.closeResource(conn, null, null);
        }
    }

    public static <N extends AbEntity, ND extends CommonDao> Set<String> queryNSetByM(ND nd, List<String> kfList, Function<N, String> columnFun) {
        DatabaseBasicService databaseBasicService = ApplicationManager.getApplication().getService(DatabaseBasicService.class);
        Connection conn = null;
        Set<String> set = Sets.newLinkedHashSet();
        try {
            conn = databaseBasicService.getSource().getConnection();
            List<N> ns = nd.queryRelate(conn, kfList);
            for (N n : ns) {
                set.add(columnFun.apply(n));
            }
            return set;
        } catch (Exception ex) {
            CodeGenByVelocityNotifier.notifyException(ex);
            return set;
        } finally {
            databaseBasicService.closeResource(conn, null, null);
        }
    }

    public static <M extends AbEntity, MD extends CommonDao> boolean batchInsert(MD md, List<M> ms) {
        DatabaseBasicService databaseBasicService = ApplicationManager.getApplication().getService(DatabaseBasicService.class);
        Connection conn = null;
        try {
            conn = databaseBasicService.getSource().getConnection();
            md.insert(conn, ListConvertUtils.toArray(ms));
            return true;
        } catch (Exception ex) {
            CodeGenByVelocityNotifier.notifyException(ex);
            return false;
        } finally {
            databaseBasicService.closeResource(conn, null, null);
        }
    }

    public static <M extends AbEntity, MD extends CommonDao> M insert(MD md, M m) {
        DatabaseBasicService databaseBasicService = ApplicationManager.getApplication().getService(DatabaseBasicService.class);
        Connection conn = null;
        try {
            conn = databaseBasicService.getSource().getConnection();
            return (M)md.insert(conn, m);
        } catch (Exception ex) {
            CodeGenByVelocityNotifier.notifyException(ex);
            return null;
        } finally {
            databaseBasicService.closeResource(conn, null, null);
        }
    }


    public static <MN extends AbEntity, N extends AbEntity, MND extends BaseDAO> Pair<Boolean, String> copyMMN(String keyM, MND mnd, List<N> ns, Class<MN> clazz, Supplier<Pair> supplier) {

        List<MN> mns = Lists.newArrayList();
        for(AbEntity abEntity : ns){
            MN mn = AbEntity.of(clazz);
            mn.fillUniqueKeyMN(keyM, abEntity.uniqueKey(), abEntity.fetchJsonValueNToMN());
            mns.add(mn);
        }
        boolean re = mnd.saveObj(mns);
        if (!re) {
            return Pair.of(false, " add sqlite fail");
        }
        if (null != supplier) {
            return supplier.get();
        }
        return Pair.of(true, null);
    }

    public static <M extends AbEntity, MD extends CommonDao> List<M> searchLike(MD md, String searchValue) {
        DatabaseBasicService databaseBasicService = ApplicationManager.getApplication().getService(DatabaseBasicService.class);
        Connection conn = null;
        try {
            conn = databaseBasicService.getSource().getConnection();
            return md.searchLike(conn, searchValue);
        } catch (Exception ex) {
            CodeGenByVelocityNotifier.notifyException(ex);
            return null;
        } finally {
            databaseBasicService.closeResource(conn, null, null);
        }
    }

}
